package gbench.sandbox.jdbc;


import java.time.LocalDateTime;
import org.junit.jupiter.api.Test;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.SQL;
import gbench.commonApp.data.DataApp;
import gbench.commonApp.jdbc.IJdbcApp;

import static gbench.common.tree.LittleTree.*;
import static gbench.common.tree.LittleTree.IRecord.*;

/**
 * 简单的EXCEL 操作
 * @author gbench
 *
 */
public class JunitExcel {
    @Test
    public void bar() {
        // 数据操作类
        final class MyDataApp extends DataApp {
            public MyDataApp(String dbname) { this.reload(this.configureDbName(dbname));}
            public IRecord configureDbName(final String dbname) { return 
                cfg.derive("url", MFT("jdbc:mysql://localhost:3306/{0}?serverTimezone=GMT%2B8", dbname));
            }
        }; // MyDataApp 数据数据库操作类
        
        final var dataApp = new MyDataApp("db1"); // 指定数据库名
        final var file = "file.xlsx";// 随便的一个excel文件
        final var excel = IJdbcApp.loadExcel(file, this.getClass()); // 加载读入excel
        final var mx = excel.autoDetect(0);// 读取表格sheet
        if(mx==null) {
            System.err.println(MFT("{0} 文件需要包含1个含有表格的sheet",file));
            return;
        }
        final var colsize = mx.reduceColumns(col->col.map(String::length).max(),IRecord::REC);// 统计列数据的信息概况(数据长度)
        System.out.println(colsize);
        
        final var tableschema = mx.getRow(0).mutate2(IRecord::REC)
        .aov2rec(e->{// 键值类型检测
            final var v = e.toString();// 获取数据的字符串类型
            if(v.matches("[0-9]+")||v.matches("\\d+\\.0+")) {// 整形类型
                return Integer.class;
            }else if(v.matches("[\\.0-9]+")){ // 浮点类型的识别
                return Double.class;
            }else if(v.matches("\\d+\\-\\d+\\-\\d+ \\d+:\\d+:\\d+")){ // 时间类型
                return LocalDateTime.class;
            }else {
                return String.class;
            }//if
        }).aoks2rec(key-> // 键名长度设置
            (("id".equalsIgnoreCase(key))?"#":"")+ // 主键标记
            ((int)Math.ceil(colsize.dbl(key)*1.3))+ // 键值长度上限 30%读懂
            key // 键名
        );// aoks2rec
        
        final var tblname = "t_user";// 指定表名
        final var builder = SQL.builder(tblname);// 创建一个SQL builder
        final var tbldefs = builder.get(tableschema).createTable(true);// 创建表定义sql语句
        final var exists_sql = MFT("show tables like ''{0}''",tblname);// 数据表是否存在
        final var select_sql = MFT("select * from {0}",tblname);// 数据表是否存在
        System.out.println(mx);
        tbldefs.forEach(System.out::println);
        
        // 数据操作
        dataApp.withTransaction(sess->{
            if(sess.sql2get(exists_sql,boolean.class)) sess.sqlexecute(MFT("drop table {0}",tblname));
            TUPLES(tbldefs).dropWhile(e->e._1()<2).map(e->e._2()).forEach(trycatch2(sess::sql2execute));
            
            mx.mapByRow(IRecord::REC).map(builder::get).map(SQL::insert).forEach(trycatch2(sess::sql2execute));
            sess.sql2records(select_sql).forEach(e->System.out.println(e.toString(frt(2))));
        });// withTransaction
        
        System.out.println("\n程序运行结束！");
    }
    
    @Test
    final void foo() {
        final var file = "file.xlsx";// 随便的一个excel文件
        final var excel = IJdbcApp.loadExcel(file, this.getClass()); // 加载读入excel
        final var mx = excel.autoDetect(0); // 读取表格sheet
        final var dataTable  = mx.reduceRows(e->REC(e.lhm()), IRecord::REC);
        
        dataTable.dfs_eval_forone((path,e)->STRING2REC(path.substring(1)) // 去除目录的根目录符号
            .keys("id,name").add("value",e)).stream() // 生成 IRecord的字段结构
        .collect(pvtclc("name,id")) // 调节IRecord 生成阶层结构
        .dfs_forone((path,v)->{
            System.out.println(MFT("{0} ---> {1}",path,v));
        });
    }

}
